<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!-- saved from url=(0028)http://jdbi.org/archive.html -->
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"></meta>


<title>abacus-common</title>

<script type="text/javascript" src="./css/ga.js"></script>
<script type="text/javascript">
	if (top.location != self.location)
		top.location.replace(self.location);
</script>

<link href="./css/css" rel="stylesheet" type="text/css"></link>
<link href="./css/css(1)" rel="stylesheet" type="text/css"></link>
<link rel="stylesheet" href="./css/syntax.css" type="text/css"></link>
<link rel="stylesheet" href="./css/screen.css" type="text/css"></link>

<script>
	(function(i, s, o, g, r, a, m) {
		i['GoogleAnalyticsObject'] = r;
		i[r] = i[r] || function() {
			(i[r].q = i[r].q || []).push(arguments)
		}, i[r].l = 1 * new Date();
		a = s.createElement(o), m = s.getElementsByTagName(o)[0];
		a.async = 1;
		a.src = g;
		m.parentNode.insertBefore(a, m)
	})(window, document, 'script', '//www.google-analytics.com/analytics.js',
			'ga');

	ga('create', 'UA-65247258-1', 'auto');
	ga('send', 'pageview');
</script>

</head>
<body>
	<div id="site">
		<h1>
			<a href="./index.html">abacus-common</a> <span id="navigation"> <a href="./docs.html">Docs</a> | <a href="./download.html">Download</a> | <a
				href="./api-docs/index.html">Javadoc</a> | <a href="./faq.html">FAQ</a> | <a href="./community.html">Community</a>
			</span>
		</h1>
	</div>

	<div id="content">

		<div id="page">

			<h1>Introduction to JDBC</h1>
			<p>
				<a href="https://en.wikipedia.org/wiki/Java_Database_Connectivity">JDBC</a> is a Java database connectivity technology. Here is a brief <a
					href="http://www.javacodegeeks.com/2015/02/jdbc-tutorial.html"><u>tutorial</u></a> and a simple sample of programming with JDBC (This sample just show you how
				Jdbc works. The below code can be simplified a lot in a real product development):
			</p>
<!-- HTML generated using hilite.me --><div style="background: #f8f8f8; overflow:auto;width:auto;border:solid gray;border-width:.1em .1em .1em .8em;padding:.2em .6em;"><pre style="margin: 0; line-height: 125%"><span style="color: #AA22FF">@Test</span>
<span style="color: #008000; font-weight: bold">public</span> <span style="color: #B00040">void</span> <span style="color: #0000FF">test_jdbc</span><span style="color: #666666">()</span> <span style="color: #008000; font-weight: bold">throws</span> Exception <span style="color: #666666">{</span>
    <span style="color: #408080; font-style: italic">// Download the specific jdbc driver from web site. Here we use H2 as a sample. </span>

    <span style="color: #008000; font-weight: bold">final</span> String driver <span style="color: #666666">=</span> <span style="color: #BA2121">&quot;org.h2.Driver&quot;</span><span style="color: #666666">;</span>
    <span style="color: #008000; font-weight: bold">final</span> String url <span style="color: #666666">=</span> <span style="color: #BA2121">&quot;jdbc:h2:tcp://localhost/~/codes&quot;</span><span style="color: #666666">;</span>
    <span style="color: #008000; font-weight: bold">final</span> String user <span style="color: #666666">=</span> <span style="color: #BA2121">&quot;SA&quot;</span><span style="color: #666666">;</span>
    <span style="color: #008000; font-weight: bold">final</span> String password <span style="color: #666666">=</span> <span style="color: #BA2121">&quot;&quot;</span><span style="color: #666666">;</span>

    DriverManager<span style="color: #666666">.</span><span style="color: #7D9029">registerDriver</span><span style="color: #666666">((</span>Driver<span style="color: #666666">)</span> N<span style="color: #666666">.</span><span style="color: #7D9029">forClass</span><span style="color: #666666">(</span>driver<span style="color: #666666">).</span><span style="color: #7D9029">newInstance</span><span style="color: #666666">());</span>

    <span style="color: #408080; font-style: italic">// insert a record into database</span>
    <span style="color: #008000; font-weight: bold">final</span> String sql_insert <span style="color: #666666">=</span> <span style="color: #BA2121">&quot;INSERT INTO account (gui, first_name, last_name, last_update_time, create_time) VALUES (?, ?, ?, ?, ?)&quot;</span><span style="color: #666666">;</span>
    <span style="color: #B00040">long</span> id <span style="color: #666666">=</span> <span style="color: #666666">0;</span>
    Connection conn <span style="color: #666666">=</span> <span style="color: #008000; font-weight: bold">null</span><span style="color: #666666">;</span>
    PreparedStatement stmt <span style="color: #666666">=</span> <span style="color: #008000; font-weight: bold">null</span><span style="color: #666666">;</span>
    ResultSet rs <span style="color: #666666">=</span> <span style="color: #008000; font-weight: bold">null</span><span style="color: #666666">;</span>

    <span style="color: #008000; font-weight: bold">try</span> <span style="color: #666666">{</span>
        <span style="color: #408080; font-style: italic">// get a connection</span>
        conn <span style="color: #666666">=</span> DriverManager<span style="color: #666666">.</span><span style="color: #7D9029">getConnection</span><span style="color: #666666">(</span>url<span style="color: #666666">,</span> user<span style="color: #666666">,</span> password<span style="color: #666666">);</span>

        <span style="color: #408080; font-style: italic">// prepare the statement by sql.</span>
        stmt <span style="color: #666666">=</span> conn<span style="color: #666666">.</span><span style="color: #7D9029">prepareStatement</span><span style="color: #666666">(</span>sql_insert<span style="color: #666666">,</span> Statement<span style="color: #666666">.</span><span style="color: #7D9029">RETURN_GENERATED_KEYS</span><span style="color: #666666">);</span>

        <span style="color: #408080; font-style: italic">// set parameters</span>
        <span style="color: #B00040">int</span> parameterIndex <span style="color: #666666">=</span> <span style="color: #666666">1;</span>
        stmt<span style="color: #666666">.</span><span style="color: #7D9029">setString</span><span style="color: #666666">(</span>parameterIndex<span style="color: #666666">++,</span> N<span style="color: #666666">.</span><span style="color: #7D9029">uuid</span><span style="color: #666666">());</span>
        stmt<span style="color: #666666">.</span><span style="color: #7D9029">setString</span><span style="color: #666666">(</span>parameterIndex<span style="color: #666666">++,</span> <span style="color: #BA2121">&quot;Jack&quot;</span><span style="color: #666666">);</span>
        stmt<span style="color: #666666">.</span><span style="color: #7D9029">setString</span><span style="color: #666666">(</span>parameterIndex<span style="color: #666666">++,</span> <span style="color: #BA2121">&quot;Ye&quot;</span><span style="color: #666666">);</span>
        stmt<span style="color: #666666">.</span><span style="color: #7D9029">setTimestamp</span><span style="color: #666666">(</span>parameterIndex<span style="color: #666666">++,</span> N<span style="color: #666666">.</span><span style="color: #7D9029">currentTimestamp</span><span style="color: #666666">());</span>
        stmt<span style="color: #666666">.</span><span style="color: #7D9029">setTimestamp</span><span style="color: #666666">(</span>parameterIndex<span style="color: #666666">++,</span> N<span style="color: #666666">.</span><span style="color: #7D9029">currentTimestamp</span><span style="color: #666666">());</span>

        stmt<span style="color: #666666">.</span><span style="color: #7D9029">execute</span><span style="color: #666666">();</span>

        rs <span style="color: #666666">=</span> stmt<span style="color: #666666">.</span><span style="color: #7D9029">getGeneratedKeys</span><span style="color: #666666">();</span>

        <span style="color: #008000; font-weight: bold">if</span> <span style="color: #666666">(</span>rs<span style="color: #666666">.</span><span style="color: #7D9029">next</span><span style="color: #666666">())</span> <span style="color: #666666">{</span>
            id <span style="color: #666666">=</span> rs<span style="color: #666666">.</span><span style="color: #7D9029">getLong</span><span style="color: #666666">(1);</span>
        <span style="color: #666666">}</span>
    <span style="color: #666666">}</span> <span style="color: #008000; font-weight: bold">finally</span> <span style="color: #666666">{</span>
        <span style="color: #408080; font-style: italic">//  close ResultSet, PreparedStatement and Connection in finally block.</span>
        closeQuietly<span style="color: #666666">(</span>rs<span style="color: #666666">,</span> stmt<span style="color: #666666">,</span> conn<span style="color: #666666">);</span>
    <span style="color: #666666">}</span>

    <span style="color: #408080; font-style: italic">// Read the inserted account from database</span>
    <span style="color: #008000; font-weight: bold">final</span> String sql_getById <span style="color: #666666">=</span> <span style="color: #BA2121">&quot;SELECT id AS \&quot;id\&quot;, gui AS \&quot;gui\&quot;, first_name AS \&quot;firstName\&quot;, last_name AS \&quot;lastName\&quot; FROM account WHERE id = ?&quot;</span><span style="color: #666666">;</span>
    Account dbAccount <span style="color: #666666">=</span> <span style="color: #008000; font-weight: bold">null</span><span style="color: #666666">;</span>
    conn <span style="color: #666666">=</span> <span style="color: #008000; font-weight: bold">null</span><span style="color: #666666">;</span>
    stmt <span style="color: #666666">=</span> <span style="color: #008000; font-weight: bold">null</span><span style="color: #666666">;</span>
    rs <span style="color: #666666">=</span> <span style="color: #008000; font-weight: bold">null</span><span style="color: #666666">;</span>

    <span style="color: #008000; font-weight: bold">try</span> <span style="color: #666666">{</span>
        conn <span style="color: #666666">=</span> DriverManager<span style="color: #666666">.</span><span style="color: #7D9029">getConnection</span><span style="color: #666666">(</span>url<span style="color: #666666">,</span> user<span style="color: #666666">,</span> password<span style="color: #666666">);</span>
        stmt <span style="color: #666666">=</span> conn<span style="color: #666666">.</span><span style="color: #7D9029">prepareStatement</span><span style="color: #666666">(</span>sql_getById<span style="color: #666666">);</span>
        stmt<span style="color: #666666">.</span><span style="color: #7D9029">setLong</span><span style="color: #666666">(1,</span> id<span style="color: #666666">);</span>

        rs <span style="color: #666666">=</span> stmt<span style="color: #666666">.</span><span style="color: #7D9029">executeQuery</span><span style="color: #666666">();</span>

        <span style="color: #008000; font-weight: bold">if</span> <span style="color: #666666">(</span>rs<span style="color: #666666">.</span><span style="color: #7D9029">next</span><span style="color: #666666">())</span> <span style="color: #666666">{</span>
            dbAccount <span style="color: #666666">=</span> <span style="color: #008000; font-weight: bold">new</span> Account<span style="color: #666666">();</span>
            dbAccount<span style="color: #666666">.</span><span style="color: #7D9029">setId</span><span style="color: #666666">(</span>rs<span style="color: #666666">.</span><span style="color: #7D9029">getLong</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;id&quot;</span><span style="color: #666666">));</span>
            dbAccount<span style="color: #666666">.</span><span style="color: #7D9029">setGUI</span><span style="color: #666666">(</span>rs<span style="color: #666666">.</span><span style="color: #7D9029">getString</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;firstName&quot;</span><span style="color: #666666">));</span>
            dbAccount<span style="color: #666666">.</span><span style="color: #7D9029">setFirstName</span><span style="color: #666666">(</span>rs<span style="color: #666666">.</span><span style="color: #7D9029">getString</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;firstName&quot;</span><span style="color: #666666">));</span>
            dbAccount<span style="color: #666666">.</span><span style="color: #7D9029">setLastName</span><span style="color: #666666">(</span>rs<span style="color: #666666">.</span><span style="color: #7D9029">getString</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;lastName&quot;</span><span style="color: #666666">));</span>
        <span style="color: #666666">}</span>
    <span style="color: #666666">}</span> <span style="color: #008000; font-weight: bold">finally</span> <span style="color: #666666">{</span>
        closeQuietly<span style="color: #666666">(</span>rs<span style="color: #666666">,</span> stmt<span style="color: #666666">,</span> conn<span style="color: #666666">);</span>
    <span style="color: #666666">}</span>

    <span style="color: #408080; font-style: italic">// Delete the inserted account from database</span>
    <span style="color: #008000; font-weight: bold">final</span> String delete_getById <span style="color: #666666">=</span> <span style="color: #BA2121">&quot;DELETE FROM account WHERE id = ?&quot;</span><span style="color: #666666">;</span>
    conn <span style="color: #666666">=</span> <span style="color: #008000; font-weight: bold">null</span><span style="color: #666666">;</span>
    stmt <span style="color: #666666">=</span> <span style="color: #008000; font-weight: bold">null</span><span style="color: #666666">;</span>
    rs <span style="color: #666666">=</span> <span style="color: #008000; font-weight: bold">null</span><span style="color: #666666">;</span>

    <span style="color: #008000; font-weight: bold">try</span> <span style="color: #666666">{</span>
        conn <span style="color: #666666">=</span> DriverManager<span style="color: #666666">.</span><span style="color: #7D9029">getConnection</span><span style="color: #666666">(</span>url<span style="color: #666666">,</span> user<span style="color: #666666">,</span> password<span style="color: #666666">);</span>
        stmt <span style="color: #666666">=</span> conn<span style="color: #666666">.</span><span style="color: #7D9029">prepareStatement</span><span style="color: #666666">(</span>delete_getById<span style="color: #666666">);</span>
        stmt<span style="color: #666666">.</span><span style="color: #7D9029">setLong</span><span style="color: #666666">(1,</span> id<span style="color: #666666">);</span>

        stmt<span style="color: #666666">.</span><span style="color: #7D9029">executeUpdate</span><span style="color: #666666">();</span>
    <span style="color: #666666">}</span> <span style="color: #008000; font-weight: bold">finally</span> <span style="color: #666666">{</span>
        closeQuietly<span style="color: #666666">(</span><span style="color: #008000; font-weight: bold">null</span><span style="color: #666666">,</span> stmt<span style="color: #666666">,</span> conn<span style="color: #666666">);</span>
    <span style="color: #666666">}</span>
<span style="color: #666666">}</span>

<span style="color: #008000; font-weight: bold">private</span> <span style="color: #B00040">void</span> <span style="color: #0000FF">closeQuietly</span><span style="color: #666666">(</span>ResultSet rs<span style="color: #666666">,</span> PreparedStatement stmt<span style="color: #666666">,</span> Connection conn<span style="color: #666666">)</span> <span style="color: #666666">{</span>
    <span style="color: #008000; font-weight: bold">if</span> <span style="color: #666666">(</span>rs <span style="color: #666666">!=</span> <span style="color: #008000; font-weight: bold">null</span><span style="color: #666666">)</span> <span style="color: #666666">{</span>
        <span style="color: #008000; font-weight: bold">try</span> <span style="color: #666666">{</span>
            rs<span style="color: #666666">.</span><span style="color: #7D9029">close</span><span style="color: #666666">();</span>
        <span style="color: #666666">}</span> <span style="color: #008000; font-weight: bold">catch</span> <span style="color: #666666">(</span>Throwable e<span style="color: #666666">)</span> <span style="color: #666666">{</span>
            <span style="color: #408080; font-style: italic">// ignore.</span>
        <span style="color: #666666">}</span>
    <span style="color: #666666">}</span>

    <span style="color: #008000; font-weight: bold">if</span> <span style="color: #666666">(</span>stmt <span style="color: #666666">!=</span> <span style="color: #008000; font-weight: bold">null</span><span style="color: #666666">)</span> <span style="color: #666666">{</span>
        <span style="color: #008000; font-weight: bold">try</span> <span style="color: #666666">{</span>
            stmt<span style="color: #666666">.</span><span style="color: #7D9029">close</span><span style="color: #666666">();</span>
        <span style="color: #666666">}</span> <span style="color: #008000; font-weight: bold">catch</span> <span style="color: #666666">(</span>Throwable e<span style="color: #666666">)</span> <span style="color: #666666">{</span>
            <span style="color: #408080; font-style: italic">// ignore.</span>
        <span style="color: #666666">}</span>
    <span style="color: #666666">}</span>

    <span style="color: #008000; font-weight: bold">if</span> <span style="color: #666666">(</span>conn <span style="color: #666666">!=</span> <span style="color: #008000; font-weight: bold">null</span><span style="color: #666666">)</span> <span style="color: #666666">{</span>
        <span style="color: #008000; font-weight: bold">try</span> <span style="color: #666666">{</span>
            conn<span style="color: #666666">.</span><span style="color: #7D9029">close</span><span style="color: #666666">();</span>
        <span style="color: #666666">}</span> <span style="color: #008000; font-weight: bold">catch</span> <span style="color: #666666">(</span>Throwable e<span style="color: #666666">)</span> <span style="color: #666666">{</span>
            <span style="color: #408080; font-style: italic">// ignore.</span>
        <span style="color: #666666">}</span>
    <span style="color: #666666">}</span>
<span style="color: #666666">}</span>
</pre></div>

		</div>
</body>

</html>